CREATE DATABASE inari_pi;

CREATE TABLE `login_log` (
  `id_log` int(11) NOT NULL,
  `ip_address` varchar(15) CHARACTER SET latin1 NOT NULL,
  `try_time` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `tbl_device` (
  `id_mac_address` int(6) NOT NULL,
  `device_name` varchar(25) NOT NULL DEFAULT 'Unknown device',
  `mac_address` varchar(17) NOT NULL,
  `ip_address` varchar(15) NOT NULL,
  `details` varchar(30) NOT NULL DEFAULT 'Take action to edit details.',
  `status` int(1) NOT NULL DEFAULT 1,
  `online` varchar(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tbl_user` (
  `id_user` int(6) NOT NULL,
  `username` varchar(30) NOT NULL,
  `password` varchar(64) NOT NULL,
  `email` varchar(35) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `login_log`
  ADD PRIMARY KEY (`id_log`);
ALTER TABLE `tbl_device`
  ADD PRIMARY KEY (`id_mac_address`);
ALTER TABLE `tbl_user`
  ADD PRIMARY KEY (`id_user`);
ALTER TABLE `login_log`
  MODIFY `id_log` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=100;
ALTER TABLE `tbl_device`
  MODIFY `id_mac_address` int(6) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1000;
ALTER TABLE `tbl_user`
  MODIFY `id_user` int(6) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
